package com.xueyu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.xueyu.model.Book;
import com.xueyu.util.StringUtil;

/**
 * 图书DAO类
 * @author xueyu
 *
 */
public class BookDao {
	/**
	 * 图书添加
	 * @param con
	 * @param book
	 * @return影响记录条数
	 * @throws Exception
	 */
	@Test
	public int add(Connection con , Book book) throws Exception{
		
		String sql = "insert into t_book values(null,?,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setInt(5, book.getBookTypeId());
		pstmt.setString(6, book.getBookDesc());
		
		return pstmt.executeUpdate();
	}
	/**
	 * 图书信息查询集合
	 * @param con
	 * @param book
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet list(Connection con,Book book) throws SQLException{
		StringBuffer sb = new StringBuffer("select * from t_book b,t_bookType bt where b.bookTypeId = bt.id");
		if(StringUtil.isNotEmpty(book.getBookName())){
			sb.append(" and bookName like "+"'"+"%"+book.getBookName()+"%"+"'");
		}
		if(StringUtil.isNotEmpty(book.getAuthor())){
			sb.append(" and author like "+"'"+"%"+book.getAuthor()+"%"+"'");
		}
		if(book.getBookTypeId()!=null && book.getBookTypeId()!=-1){
			sb.append(" and b.bookTypeId="+book.getBookTypeId());
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}
	/**
	 * 图书修改
	 * @param con
	 * @param book
	 * @return
	 * @throws SQLException 
	 */
	public int update(Connection con , Book book) throws SQLException{
		String sql = "update t_book set bookName = ?,author = ?,sex = ?,price = ?,bookDesc = ? ,bookTypeId = ? where id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setString(5, book.getBookDesc());
		pstmt.setInt(6, book.getBookTypeId());
		pstmt.setInt(7, book.getId());
		return pstmt.executeUpdate();
	}
	/**
	 * 图书删除
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public int delete(Connection con , Book book) throws Exception{
		String sql = "delete from t_book  where id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, book.getId());
		return pstmt.executeUpdate();
	}
	/**
	 * 判断某一图书类别下是否有图书
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public boolean isExistbook(Connection con , String bookTypeId)throws Exception{
		String sql = "select * from t_book where bookTypeId =" + bookTypeId;
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		return rs.next();
	}
}
